package com.no1.utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库帮助类
 */
public class DBHelper {
    //数据库连接配置
    private static String url;
    private static String driver;
    private static String user;
    private static String password;
    private static Connection connection;
    private static PreparedStatement statement;
    private static ResultSet result;

    static {
        //初始化连接参数
        url = "jdbc:mysql://localhost:3306/erp";
        driver = "com.mysql.jdbc.Driver";
        user = "root";
        password = "1111";
        getConnection();
    }

    //取得数据库连接对象
    public static Connection getConnection() {
        try {
            if (connection == null) {
                Class.forName(driver);
                connection = DriverManager.getConnection(url, user, password);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //查询受影响的行数
    public static int executeUpdate(String sql, List<?> params) {
        int flag = 0;
        try {
            statement = connection.prepareStatement(sql);
            for (int i = 0; params!=null&&i < params.size(); i++) {
                statement.setObject(i+1, params.get(i));
            }
            flag = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            //return 0;
        }
        return flag;
    }

    //获取结果集
    public static List<Map<String, Object>> executeQuery(String sql, List<?> params) {
        List<Map<String, Object>> values = new ArrayList<>();
        try {
            statement = connection.prepareStatement(sql);
            for (int i = 0; params!=null&&i<params.size(); i++) {
                statement.setObject(i+1, params.get(i));
            }
            result = statement.executeQuery();
            ResultSetMetaData metaData = result.getMetaData();
            int cols_len = metaData.getColumnCount();
            while (result.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < cols_len; i++) {
                    String cols_name = metaData.getColumnName(i + 1);
                    Object cols_value = result.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_name, cols_value);
                }
                values.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            //return values;
        }
        return values;
    }
    //释放连接
    public static void closeConnection(){
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(result!=null){
            try {
                result.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
